{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "440d361e-f41e-494a-98a3-aa87cf0087af",
   "metadata": {
    "ExecuteTime": {
     "start_time": "2023-12-22T09:29:05.967568Z",
     "end_time": "2023-12-22T09:29:06.824582Z"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [
    {
     "data": {
      "text/plain": "       InvoiceNo StockCode                          Description  Quantity  \\\n0         536365    85123A   WHITE HANGING HEART T-LIGHT HOLDER         6   \n1         536365     71053                  WHITE METAL LANTERN         6   \n2         536365    84406B       CREAM CUPID HEARTS COAT HANGER         8   \n3         536365    84029G  KNITTED UNION FLAG HOT WATER BOTTLE         6   \n4         536365    84029E       RED WOOLLY HOTTIE WHITE HEART.         6   \n...          ...       ...                                  ...       ...   \n541904    581587     22613          PACK OF 20 SPACEBOY NAPKINS        12   \n541905    581587     22899         CHILDREN'S APRON DOLLY GIRL          6   \n541906    581587     23254        CHILDRENS CUTLERY DOLLY GIRL          4   \n541907    581587     23255      CHILDRENS CUTLERY CIRCUS PARADE         4   \n541908    581587     22138        BAKING SET 9 PIECE RETROSPOT          3   \n\n               InvoiceDate  UnitPrice  CustomerID         Country  \n0      2010-12-01 08:26:00       2.55     17850.0  United Kingdom  \n1      2010-12-01 08:26:00       3.39     17850.0  United Kingdom  \n2      2010-12-01 08:26:00       2.75     17850.0  United Kingdom  \n3      2010-12-01 08:26:00       3.39     17850.0  United Kingdom  \n4      2010-12-01 08:26:00       3.39     17850.0  United Kingdom  \n...                    ...        ...         ...             ...  \n541904 2011-12-09 12:50:00       0.85     12680.0          France  \n541905 2011-12-09 12:50:00       2.10     12680.0          France  \n541906 2011-12-09 12:50:00       4.15     12680.0          France  \n541907 2011-12-09 12:50:00       4.15     12680.0          France  \n541908 2011-12-09 12:50:00       4.95     12680.0          France  \n\n[541909 rows x 8 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>InvoiceNo</th>\n      <th>StockCode</th>\n      <th>Description</th>\n      <th>Quantity</th>\n      <th>InvoiceDate</th>\n      <th>UnitPrice</th>\n      <th>CustomerID</th>\n      <th>Country</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>536365</td>\n      <td>85123A</td>\n      <td>WHITE HANGING HEART T-LIGHT HOLDER</td>\n      <td>6</td>\n      <td>2010-12-01 08:26:00</td>\n      <td>2.55</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>536365</td>\n      <td>71053</td>\n      <td>WHITE METAL LANTERN</td>\n      <td>6</td>\n      <td>2010-12-01 08:26:00</td>\n      <td>3.39</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>536365</td>\n      <td>84406B</td>\n      <td>CREAM CUPID HEARTS COAT HANGER</td>\n      <td>8</td>\n      <td>2010-12-01 08:26:00</td>\n      <td>2.75</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>536365</td>\n      <td>84029G</td>\n      <td>KNITTED UNION FLAG HOT WATER BOTTLE</td>\n      <td>6</td>\n      <td>2010-12-01 08:26:00</td>\n      <td>3.39</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>536365</td>\n      <td>84029E</td>\n      <td>RED WOOLLY HOTTIE WHITE HEART.</td>\n      <td>6</td>\n      <td>2010-12-01 08:26:00</td>\n      <td>3.39</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>541904</th>\n      <td>581587</td>\n      <td>22613</td>\n      <td>PACK OF 20 SPACEBOY NAPKINS</td>\n      <td>12</td>\n      <td>2011-12-09 12:50:00</td>\n      <td>0.85</td>\n      <td>12680.0</td>\n      <td>France</td>\n    </tr>\n    <tr>\n      <th>541905</th>\n      <td>581587</td>\n      <td>22899</td>\n      <td>CHILDREN'S APRON DOLLY GIRL</td>\n      <td>6</td>\n      <td>2011-12-09 12:50:00</td>\n      <td>2.10</td>\n      <td>12680.0</td>\n      <td>France</td>\n    </tr>\n    <tr>\n      <th>541906</th>\n      <td>581587</td>\n      <td>23254</td>\n      <td>CHILDRENS CUTLERY DOLLY GIRL</td>\n      <td>4</td>\n      <td>2011-12-09 12:50:00</td>\n      <td>4.15</td>\n      <td>12680.0</td>\n      <td>France</td>\n    </tr>\n    <tr>\n      <th>541907</th>\n      <td>581587</td>\n      <td>23255</td>\n      <td>CHILDRENS CUTLERY CIRCUS PARADE</td>\n      <td>4</td>\n      <td>2011-12-09 12:50:00</td>\n      <td>4.15</td>\n      <td>12680.0</td>\n      <td>France</td>\n    </tr>\n    <tr>\n      <th>541908</th>\n      <td>581587</td>\n      <td>22138</td>\n      <td>BAKING SET 9 PIECE RETROSPOT</td>\n      <td>3</td>\n      <td>2011-12-09 12:50:00</td>\n      <td>4.95</td>\n      <td>12680.0</td>\n      <td>France</td>\n    </tr>\n  </tbody>\n</table>\n<p>541909 rows × 8 columns</p>\n</div>"
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('Online Retail.xlsx')\n",
    "df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-12-22T09:29:06.825089Z",
     "end_time": "2023-12-22T09:29:53.752269Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [
    {
     "data": {
      "text/plain": "             日期       销售额\n0    2010-12-01  58635.56\n1    2010-12-02  46207.28\n2    2010-12-03  45620.46\n3    2010-12-05  31383.95\n4    2010-12-06  53860.18\n..          ...       ...\n300  2011-12-05  57751.32\n301  2011-12-06  54228.37\n302  2011-12-07  75076.22\n303  2011-12-08  81417.78\n304  2011-12-09  32131.53\n\n[305 rows x 2 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>日期</th>\n      <th>销售额</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2010-12-01</td>\n      <td>58635.56</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2010-12-02</td>\n      <td>46207.28</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2010-12-03</td>\n      <td>45620.46</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2010-12-05</td>\n      <td>31383.95</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2010-12-06</td>\n      <td>53860.18</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>300</th>\n      <td>2011-12-05</td>\n      <td>57751.32</td>\n    </tr>\n    <tr>\n      <th>301</th>\n      <td>2011-12-06</td>\n      <td>54228.37</td>\n    </tr>\n    <tr>\n      <th>302</th>\n      <td>2011-12-07</td>\n      <td>75076.22</td>\n    </tr>\n    <tr>\n      <th>303</th>\n      <td>2011-12-08</td>\n      <td>81417.78</td>\n    </tr>\n    <tr>\n      <th>304</th>\n      <td>2011-12-09</td>\n      <td>32131.53</td>\n    </tr>\n  </tbody>\n</table>\n<p>305 rows × 2 columns</p>\n</div>"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 从日期列中提取日期\n",
    "df['日期'] = df['InvoiceDate'].dt.to_period('D').astype(str)\n",
    "# 计算销售额\n",
    "df['销售额'] = df['Quantity'] * df['UnitPrice']\n",
    "# 汇总每天的销售额\n",
    "df_daily = pd.DataFrame(df.groupby('日期')['销售额'].agg('sum')).reset_index()\n",
    "df_daily"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-12-22T09:29:53.877281Z",
     "end_time": "2023-12-22T09:29:54.297503Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "from openpyxl.utils.dataframe import dataframe_to_rows\n",
    "from openpyxl.styles import Font, Color, NamedStyle, Border, Side, PatternFill, Alignment, numbers\n",
    "\n",
    "# 创建工作簿\n",
    "wb = openpyxl.Workbook()\n",
    "# 激活工作表\n",
    "ws = wb.active\n",
    "# 重命名工作表\n",
    "ws.title = '每日销售额'\n",
    "# 把数据写入工作表\n",
    "for row in dataframe_to_rows(df_daily, index=False, header=True):\n",
    "    ws.append(row)\n",
    "\n",
    "# 创建自定义的标题样式\n",
    "mytitle = NamedStyle(name='mytitle')\n",
    "mytitle.font = Font(bold=True, size=11, color='FFFFFF')\n",
    "bd = Side(style='thin', color='A6A6A6')\n",
    "mytitle.border = Border(left=bd, top=bd, right=bd, bottom=bd)\n",
    "mytitle.fill = PatternFill('solid', fgColor='00589F')\n",
    "mytitle.alignment = Alignment(horizontal='left', vertical='center')\n",
    "\n",
    "# 创建自定义表格边框样式\n",
    "myborder = NamedStyle(name='myborder')\n",
    "myborder.font = Font(bold=False, size=11, color='000000')\n",
    "bd = Side(style='thin', color='A6A6A6')\n",
    "myborder.border = Border(left=bd, top=bd, right=bd, bottom=bd)\n",
    "myborder.alignment = Alignment(vertical='center')\n",
    "wb.add_named_style(myborder)\n",
    "\n",
    "# 应用标题样式\n",
    "for cell in ws[1]:\n",
    "    cell.style = mytitle\n",
    "\n",
    "# 对表格区域加边框\n",
    "from openpyxl.utils import get_column_letter, column_index_from_string\n",
    "\n",
    "table_range = ws['A2:' + get_column_letter(ws.max_column) + str(ws.max_row)]\n",
    "for row in table_range:\n",
    "    for cell in row:\n",
    "        cell.style = myborder\n",
    "\n",
    "# 设置行高和列宽\n",
    "ws.row_dimensions[1].height = 26\n",
    "ws.column_dimensions['A'].width = 15\n",
    "ws.column_dimensions['B'].width = 12\n",
    "\n",
    "# 设置不显示网格线\n",
    "ws.views.sheetView[0].showGridLines = False\n",
    "\n",
    "# 冻结窗格\n",
    "ws.freeze_panes = 'A2'\n",
    "\n",
    "# 自动筛选\n",
    "ws.auto_filter.ref = 'A1:' + get_column_letter(ws.max_column) + str(ws.max_row)\n",
    "\n",
    "# 设置对齐格式\n",
    "for cell in ws['A']:\n",
    "    cell.alignment = Alignment(horizontal='center', vertical='center')\n",
    "\n",
    "# 设置日期格式\n",
    "for cell in ws['A']:\n",
    "    cell.number_format = numbers.FORMAT_DATE_YYYYMMDD2\n",
    "\n",
    "# 设置数字格式\n",
    "for cell in ws['B']:\n",
    "    cell.number_format = numbers.BUILTIN_FORMATS[3]\n",
    "\n",
    "# 保存为新的表格\n",
    "wb.save('每日销售报表.xlsx')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-12-22T10:18:53.572890Z",
     "end_time": "2023-12-22T10:18:53.616844Z"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
